package com.geek.jdbc.trans;

import com.geek.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;

/**
 * @Desc TODO
 * @Author
 * @Date 2025/11/17
 *
 * 事务
 * 1.事务 ACID  原子性、一致性、隔离性、持久性
 * 2.rollback 、 commit 、 savepoint
 * 3.在业务方法中，添加事务的管理 XxxService类
 * 4.如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即**同一个事务的多个操作必须在同一个连接下**。
 *
 * 事务的操作
 * 1. 取消自动提交事务 conn.setAutoCommit(false);
 * 2. catch (Exception e) {
 *             //事务回滚
 *             try {
 *                 conn.rollback(savepointA);
 *             } catch (SQLException ex) {
 *                 ex.printStackTrace();
 *             }
 *             e.printStackTrace();
 * }
 * 3.finally {
 *             //提交事务，且设置恢复自动提交事务
 *             try {
 *                 conn.commit();
 *                 conn.setAutoCommit(true);
 *             } catch (SQLException e) {
 *                 e.printStackTrace();
 *             }
 *             JDBCUtils.close(conn , ppst , null);
 * }
 * 4.设置保存点 Savepoint savepointA = conn.setSavepoint("A");
 */
public class TransDemo {

    //未添加事务
    @Test
    public void unTrans(){
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        try {
            String sql = "update account set balance = ? where name=?";
            ppst = conn.prepareStatement(sql);

            //转出账户 张三转出1000.0
            ppst.setDouble(1 ,1000.0);
            ppst.setString(2 ,"张三");
            int i = ppst.executeUpdate();
            System.out.println("i = "+i);

            //模拟异常发生
            int a = 10/0;

            //转入账户 李四转入1000.0
            ppst.setDouble(1 ,1000.0);
            ppst.setString(2 ,"李四");
            int j = ppst.executeUpdate();
            System.out.println("j = "+j);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(ppst , conn , null);
        }
    }

    //添加事务
    @Test
    public void trans(){
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst1 = null;
        PreparedStatement ppst2 = null;
        Savepoint A = null;
        Savepoint B = null;
        Savepoint C = null;
        try {
            //TODO 1.取消事务的自动提交
            conn.setAutoCommit(false);

            String sql1 = "update account set balance = ? where name=?";
            String sql2 = "insert into account values(?,?,?)";
            ppst1 = conn.prepareStatement(sql1);
            ppst2 = conn.prepareStatement(sql2);

            //设置保存点
            A = conn.setSavepoint("A");
            //插入数据
            ppst2.setInt(1 ,4);
            ppst2.setString(2 , "赵六");
            ppst2.setDouble(3 ,8888.0);
            int k = ppst2.executeUpdate();
            System.out.println("k = "+k);


            B = conn.setSavepoint("B");
            //转出账户 张三转出1000.0
            ppst1.setDouble(1 ,1000.0);
            ppst1.setString(2 ,"张三");
            int i = ppst1.executeUpdate();
            System.out.println("i = "+i);

            //模拟异常发生
            int a = 10/0;

            C  = conn.setSavepoint("C");
            //转入账户 李四转入1000.0
            ppst1.setDouble(1 ,4000.0);
            ppst1.setString(2 ,"李四");
            int j = ppst1.executeUpdate();
            System.out.println("j = "+j);

        } catch (Exception e) {
            //TODO 2.事务回滚
            try {
                conn.rollback(B);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            e.printStackTrace();
        } finally {
            //TODO 3.提交事务 & 恢复事务的自动提交
            try {
                conn.commit();
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            //JDBCUtils.close(ppst , conn , null);
        }
    }

}
